package com.kantboot.business.water.repository;

import com.kantboot.business.water.domain.dto.BusWaterLiftingPointSearchDTO;
import com.kantboot.business.water.domain.dto.BusWaterLiftingPointSearchFrontDTO;
import com.kantboot.business.water.domain.entity.BusWaterLiftingPoint;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * 水站提水点
 */
public interface BusWaterLiftingPointRepository extends JpaRepository<BusWaterLiftingPoint,Long> {

    @Query(value = """
            SELECT p,(6371 * acos (
                cos ( radians(:#{#param.lat}) )
                * cos( radians( CAST(p.lat AS double) ) )
                * cos( radians( CAST(p.lng AS double) ) - radians(:#{#param.lng}) )
                + sin ( radians(:#{#param.lat}) )
                * sin( radians( CAST(p.lat AS double) ) ) 
            )) AS distance
            FROM BusWaterLiftingPoint p 
            WHERE (:#{#param.name} IS NULL OR :#{#param.name} = '' OR p.name LIKE CONCAT('%',:#{#param.name},'%'))
            AND (:#{#param.address} IS NULL OR :#{#param.address} = '' OR p.address LIKE CONCAT('%',:#{#param.address},'%'))
            AND (:#{#param.adlv1Name} IS NULL OR :#{#param.adlv1Name} = '' OR p.adlv1Name LIKE CONCAT('%',:#{#param.adlv1Name},'%'))
            AND (:#{#param.adlv2Name} IS NULL OR :#{#param.adlv2Name} = '' OR p.adlv2Name LIKE CONCAT('%',:#{#param.adlv2Name},'%'))
            AND (:#{#param.adlv3Name} IS NULL OR :#{#param.adlv3Name} = '' OR p.adlv3Name LIKE CONCAT('%',:#{#param.adlv3Name},'%'))
            ORDER BY distance ASC
            """)
    Page<BusWaterLiftingPoint> getBodyData(
            @Param("param") BusWaterLiftingPointSearchDTO dto, Pageable pageable);

    @Query(value = """
            SELECT p,(6371 * acos (
                cos ( radians(:#{#param.lat}) )
                * cos( radians( CAST(p.lat AS double) ) )
                * cos( radians( CAST(p.lng AS double) ) - radians(:#{#param.lng}) )
                + sin ( radians(:#{#param.lat}) )
                * sin( radians( CAST(p.lat AS double) ) ) 
            )) AS distance
            FROM BusWaterLiftingPoint p 
            LEFT JOIN BusWaterUserLiftingPoint ulp ON p.id = ulp.liftingPointId
            WHERE (:#{#param.keyword} IS NULL OR :#{#param.keyword} = '' OR p.name LIKE CONCAT('%',:#{#param.keyword},'%')
                OR p.address LIKE CONCAT('%',:#{#param.keyword},'%') OR p.adlv1Name LIKE CONCAT('%',:#{#param.keyword},'%')
                OR p.adlv2Name LIKE CONCAT('%',:#{#param.keyword},'%') OR p.adlv3Name LIKE CONCAT('%',:#{#param.keyword},'%')
            )
            AND (:#{#param.userIds} IS NULL OR ulp.userId IN (:#{#param.userIds}))
            ORDER BY distance ASC
            """)
    Page<BusWaterLiftingPoint> getBodyDataFront(
                    @Param("param") BusWaterLiftingPointSearchFrontDTO dto, Pageable pageable);

    /**
     * 根据用户id获取该用户的提水点
     */
    @Query("""
            SELECT p
            FROM BusWaterLiftingPoint p
            LEFT JOIN BusWaterUserLiftingPoint ulp ON p.id = ulp.liftingPointId
            WHERE ulp.userId = :userId
            """)
    List<BusWaterLiftingPoint> getByUserId(@Param("userId") Long userId);

}
